<?php

/**
 * Controlador padrão, que executa as ações básicas da tabela.
 * 
 * @author Bruno E. Fuhr [brunofuhr@gmail.com]
 * @since 04/03/2013
 */

require_once 'FController.class.php';
require_once 'FUtil.class.php';

class FDefaultController extends FController
{
    
    protected $searchTerm;
    
    public function __construct($dbName = 'system')
    {
        parent::__construct($dbName);
    }
    
    public function search()
    {
        $sql = new FSQL();
        $sql->addTable($this->getTable());
        
        $primaryKeys = $this->obterChavesPrimariasDaTabela();
        $pks = array();
        foreach ( $primaryKeys as $primaryKey )
        {
            $sql->addColumn($this->table . '.' . $primaryKey['name']);
            $pks[] = $primaryKey['name'];
        }

        foreach( $this->columns as $coluna )
        {                        
            if ( $foreignKey = $this->getForeignKey($coluna) )
            {
                $foreignController = new FDefaultController();
                $foreignController->setTable($foreignKey['table_ref']);
                $foreignDescription = $foreignController->getDescriptionColumn();
                
                if ( !in_array($coluna, $pks) )
                {
                    $sql->addColumn($foreignKey['table_ref'] . '.' . $foreignDescription . ' AS ' . $coluna);
                    $sql->addLeftJoin($foreignKey['table_ref'], $this->table . '.' . $coluna . ' = ' . $foreignKey['table_ref'] . '.' . $foreignKey['attribute_ref']);
                }
                else
                {
                    $sql->addColumn($foreignKey['table_ref'] . '.' . $foreignDescription . ' AS ' . $foreignKey['table_ref'] . '$' . $foreignDescription);
                    $sql->addLeftJoin($foreignKey['table_ref'], $this->table . '.' . $coluna . ' = ' . $foreignKey['table_ref'] . '.' . $foreignKey['attribute_ref']);
                }
            }
            else
            {
                if ( !in_array($coluna, $pks) )
                {
                    $sql->addColumn($this->table . '.' . $coluna);
                }
            }
        }

        
        // Se tiver o termo de pesquisa, veio de um SearchForm. Pesquisar o termo em todos os campos.
        if ( strlen($this->getSearchTerm()) > 0 )
        {
            $filter = $this->getSearchTerm();

            foreach($this->columns as $coluna)
            {                
                $colunaInfo = $this->obterInformacaoDaColuna($coluna, $this->getTable());
                $colunaInfo instanceof FColumnInfo;                

                if ( $colunaInfo->getType() == FController::TYPE_INT || 
                     $colunaInfo->getType() == FController::TYPE_INTEGER || 
                     $colunaInfo->getType() == FController::TYPE_BIGINT || 
                     $colunaInfo->getType() == FController::TYPE_FLOAT ||
                     $colunaInfo->getType() == FController::TYPE_NUMERIC || 
                     $colunaInfo->getType() == FController::TYPE_DOUBLE )
                {
                    if (is_numeric($filter) )
                    {
                        $sql->setWhereOr("{$this->table}.{$coluna} = ?", $filter);
                    }
                }
                elseif ( $colunaInfo->getType() == FController::TYPE_DATE || $colunaInfo->getType() == FController::TYPE_TIMESTAMP )
                {
                    if ( FUtil::validateData($filter) )
                    {
                        $sql->setWhereOr("{$this->table}.{$coluna} = '?'", $filter);
                    }
                }
                elseif ( $colunaInfo->getType() == FController::TYPE_BOOLEAN )
                {
                    if ( FUtil::isBoolean($filter) )
                    {
                        $sql->setWhereOr("{$this->table}.{$coluna} = '?'", $filter);
                    }
                }
                else
                {
                    $sql->setWhereOr("lower({$this->table}.{$coluna}) LIKE lower($$%?%$$)", $filter);
                }
            }
        }
        
        // Aplica os filtros dinamicamente.        
        foreach( $this->filters as $key => $filter )
        {
            foreach( $this->columns as $coluna )
            {
                if ( $coluna == $key )
                {
                    if ( strlen($filter) > 0 )
                    {
                        $colunaInfo = $this->obterInformacaoDaColuna($coluna, $this->getTable());
                        $colunaInfo instanceof FColumnInfo;
                        
                        if ( $colunaInfo->getType() == FController::TYPE_INT || 
                             $colunaInfo->getType() == FController::TYPE_INTEGER || 
                             $colunaInfo->getType() == FController::TYPE_BIGINT || 
                             $colunaInfo->getType() == FController::TYPE_FLOAT ||
                             $colunaInfo->getType() == FController::TYPE_NUMERIC || 
                             $colunaInfo->getType() == FController::TYPE_DOUBLE )
                        {
                            $sql->setWhereAnd("{$this->table}.{$coluna} = ?", $filter);
                        }
                        elseif ( $colunaInfo->getType() == FController::TYPE_DATE || $colunaInfo->getType() == FController::TYPE_TIMESTAMP )
                        {
                            $sql->setWhereAnd("{$this->table}.{$coluna} = '?'", $filter);
                        }
                        elseif ( $colunaInfo->getType() == FController::TYPE_BOOLEAN )
                        {
                            if ( strlen($filter) == 1 )
                            {
                                $sql->setWhereAnd("{$this->table}.{$coluna} = '?'", $filter);
                            }
                            else
                            {
                                $sql->setWhereAnd("{$this->table}.{$coluna} = ?", $filter);
                            }
                        }
                        else
                        {
                            $sql->setWhereAnd("lower({$this->table}.{$coluna}) LIKE lower('%?%')", $filter);
                        }
                    }
                }
            }
        }

        return $this->query($sql->select());
    }
    
    public function insert()
    {
        $id = NULL;
        
        $sql = new FSQL();
        $sql->addTable($this->getTable());
        
        $chavesDaTabela = $this->obterChavesPrimariasDaTabela($this->getTable());
        foreach ( $chavesDaTabela as $chave )
        {
            $filters = array_keys($this->filters);

            if ( !in_array($chave['name'], $filters) )
            {
                $id[$chave['name']] = $this->obterIdDaTabela($this->getTable(), $chave['name']);
                $this->filters[$chave['name']] = $id;
            }
        }
        
        foreach( $this->filters as $key => $valorDoCampo )
        {
            foreach ( $this->columns as $coluna )
            {
                if ( $coluna == $key )
                {
                    $info = $this->obterInformacaoDaColuna($coluna, $this->getTable());
                    $info instanceof FColumnInfo;

                    if ( $info->getType() == FController::TYPE_INT || $info->getType() == FController::TYPE_BIGINT || $info->getType() == FController::TYPE_INTEGER )
                    {
                        if ( is_numeric($valorDoCampo) && !$info->isAutoIncrement() )
                        {
                            $sql->addColumn($coluna);
                            $sql->addParameter($valorDoCampo);
                        }
                    }
                    elseif ( $info->getType() == FController::TYPE_DATE )
                    {
                        if ( FUtil::validateData($valorDoCampo) )
                        {
                            $sql->addColumn($coluna);
                            $sql->addParameter($valorDoCampo);
                        }
                    }
                    else
                    {
                        $sql->addColumn($coluna);
                        $sql->addParameter($valorDoCampo);
                    }
                }
            }
        }
        
        $sqlInsert = $sql->insert();
        
        $chaves = array();
        foreach( $chavesDaTabela as $chave )
        {
            $chaves[] = $chave['name'];
        }
        
        if ( count($chaves) > 0 )
        {
            $sqlInsert .= " RETURNING " . implode(',', $chaves);
            $result = $this->query($sqlInsert);
            if (is_array($result) )
            {
                $result = $result[0];
            }
        }
        else
        {
            $result = $this->execute($sqlInsert);
        }
        
        if ( !$result )
        {
            $id = false;
        }
        else
        {
            if ( is_array($result) )
            {
                // TODO: Só funciona com tabelas com chave primária única.
                $id = $result[$chaves[0]];
                
                $foreignTables = $this->obterTabelasEstrangeiras($this->getTable());
                foreach ( $foreignTables as $foreignTable )
                {
                    $session = new FSessionManager($foreignTable['table']);
                    $data = $session->getDataAt($this->getTable());
                    foreach ( $data as $line )
                    {
                        if ( $line instanceof FDetailRow )
                        {
                            $foreignController = new FDefaultController();
                            $foreignController->setTable($foreignTable['table']);

                            $foreignController->setFilters($line->getData());
                            $foreignController->addFilter($chaves[0], $id);

                            $foreignController->insert();
                        }
                    }
                    
                    $session->setDataAt($this->getTable(), NULL);
                }
            }
            else
            {
                $id = true;
            }
        }

        return $id;
    }
    
    public function update()
    {
        $sql = new FSQL();
        $sql->addTable($this->getTable());        
        
        foreach ( $this->filters as $key => $valorDoCampo )
        {
            foreach ( $this->columns as $coluna )
            {
                if ( $coluna == $key )
                {
                    $info = $this->obterInformacaoDaColuna($coluna, $this->getTable());
                    $info instanceof FColumnInfo;
                    
                    if ( $info->getType() == FController::TYPE_INT || $info->getType() == FController::TYPE_BIGINT || $info->getType() == FController::TYPE_INTEGER )
                    {
                        if ( is_numeric($valorDoCampo) && !$info->isAutoIncrement() )
                        {
                            $sql->addColumn($coluna);
                            $sql->addParameter($valorDoCampo);
                        }
                        elseif ( !$info->isAutoIncrement() )
                        {
                            // Se o campo não tiver valor e não for not null.
                            if ( strlen($valorDoCampo) == 0 && !$info->getNotNull() )
                            {
                                $sql->addColumn($coluna);
                                $sql->addParameter(FSQL::NULL_VALUE);
                            }
                        }
                    }
                    elseif ( $info->getType() == FController::TYPE_DATE )
                    {
                        if ( FUtil::validateData($valorDoCampo) )
                        {
                            $sql->addColumn($coluna);
                            $sql->addParameter($valorDoCampo);
                        }
                    }
                    else
                    {
                        $sql->addColumn($coluna);
                        $sql->addParameter($valorDoCampo);
                    }
                }
            }
        }
        
        if ( substr_count($this->filters['idxName'], ',') > 0 )
        {
            $indexes = explode(',', $this->filters['idxName']);
            $ids = explode(',', $this->filters['dataId']);
            
            foreach ( $indexes as $key => $index )
            {
                $sql->setWhere($index . " = '?'", $ids[$key]);
            }
        }
        else
        {
            $sql->setWhere($this->filters['idxName'] . " = '?'", $this->filters['dataId']);
        }

        return $this->execute($sql->update());
    }
    
    public function delete($ids)
    {
        $delete = false;
                
        if ( substr_count($this->filters['idxName'], ',') > 0 )
        {
            $indexes = explode(',', $this->filters['idxName']);

            foreach ( $ids as $id )
            {
                $sql = new FSQL();
                $sql->addTable($this->getTable());
                
                $idValues = explode(',', $id);
                foreach ( $indexes as $key => $index )
                {
                    $sql->setWhere($index . " = '?'", $idValues[$key]);
                }
                
                $delete = $this->execute($sql->delete());
            }
        }
        else
        {
            $sql = new FSQL();
            $sql->addTable($this->getTable());
            
            $chavesDaTabela = $this->obterChavesPrimariasDaTabela($this->getTable());
            foreach ( $chavesDaTabela as $chave )
            {
                $sql->setWhere($chave['name'] . " IN ('?') ", implode("','", $ids));
            }
            
            $delete = $this->execute($sql->delete());
        }

        return $delete;
    }
    
    public function get()
    {
        $sql = new FSQL();
        $sql->addTable($this->getTable());
        $sql->setColumns($this->listarColunasDaTabela());
        
        if ( substr_count($this->filters['idxName'], ',') > 0 )
        {
            $indexes = explode(',', $this->filters['idxName']);
            $ids = explode(',', $this->filters['dataId']);
            
            foreach ( $indexes as $key => $index )
            {
                $sql->setWhere($index . " = '?'", $ids[$key]);
            }
        }
        else
        {
            $sql->setWhere($this->filters['idxName'] . " = '?'", $this->filters['dataId']);
        }

        $data = $this->query($sql->select());        
        return $data[0];
    }
    
    public function getList()
    {
        $sql = new FSQL();
        $sql->addTable($this->getTable());

        foreach( $this->columns as $coluna )
        {
            $sql->addColumn($coluna);
        }
        
        return $this->query($sql->select());
    }
    
    /**
     * Função que retorna o nome da coluna que contém a descrição do item.
     * 
     * @return String
     */
    public function getDescriptionColumn()
    {
        $descriptionColumn = NULL;
        
        $columns = $this->listarColunasDaTabela($this->getTable());        
        $primaryKeys = $this->obterChavesPrimariasDaTabela($this->getTable());
        
        foreach ( $columns as $column )
        {
            foreach ( $primaryKeys as $primaryKey )
            {
                if ( $column != $primaryKey['name'] && !$descriptionColumn )
                {
                    $descriptionColumn = $column;
                }
            }
        }
        
        return $descriptionColumn;
    }
    
    /**
     * Retorna o número de registros da tabela.
     * 
     * @return int - Número de registros da tabela.
     */
    public function getCount()
    {
        $count = 0;
        
        $sql = new FSQL();
        $sql->addTable($this->getTable());
        $sql->addColumn('count(*)');
        
        $data = $this->query($sql->select());
        $count = $data[0]['count'];
        
        return $count;
    }
    
    public function setFilters($filters)
    {
        parent::setFilters($filters);
        
        foreach ( $this->filters as $key => $filter )
        {
            if ( substr_count($key, 'fldSearch') > 0 )
            {
                if ( strlen($this->filters[$key]) > 0 )
                {
                    $this->setSearchTerm($this->filters[$key]);
                }
            }
        }
    }
    
    public function getSearchTerm() {
        return $this->searchTerm;
    }

    public function setSearchTerm($searchTerm) {
        $this->searchTerm = $searchTerm;
    }
    
}

?>
